package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryHistoryDao;
import com.boss.salary.entity.SalaryHistory;
import com.boss.salary.entity.SalaryRental;
import com.boss.salary.vo.SalaryColumnWithStateVO;
import com.boss.salary.vo.SalaryMonthEndVO;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class SalaryHistoryDaoImpl extends BaseDaoImpl implements ISalaryHistoryDao {

    @Override
    public void batchSaveSalaryHistory(List<Object[]> salaryHistoryList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_HISTORY)
                .append(" ( id,  history_key, create_time, update_time, salary_month, agency_id, person_id, is_thirteen,mof_div_code,")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, ")
                .append(" s39, s40, s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, ")
                .append(" s57, s58, s59, s60, s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, ")
                .append(" s75, s76, s77, s78, s79, s80, s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, ")
                .append(" s93, s94, s95, s96, s97, s98, s99, s100 ) ")
                .append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ")
                .append(" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ")
                .append(" ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return salaryHistoryList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = salaryHistoryList.get(i);
                for(int a = 0; a < 109; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deleteSalaryHistory(String month, List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ").append(TableName.SALARY_HISTORY);
        sql.append(" WHERE salary_month = ? and ").append(SysUtils.inSql("agency_id", agencyIds));
        this.getJdbcTemplate().update(sql.toString(), new Object[]{month});
    }

    @Override
    public void deleteThirteenData() {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.SALARY_MONTH_END);
        sql.append(" where is_thirteen = 1 ");
        this.getJdbcTemplate().update(sql.toString());
    }

    @Override
    public String getSalaryName(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("select b.salary_code columnName from ")
//                .append(TableName.AGENCY_SALARY).append(" as a")
//                .append(" left join ")
                .append(TableName.SALARY_BASEDATA).append(" b")
//                .append(" on b.id=a.salary_id and b.is_deleted=0 ")
                .append(" where b.is_deleted=0   and  b.id='").append(id).append("'");
        return super.getjdbcTemplate().queryForObject(sql.toString(), String.class);
    }

    @Override
    public List<SalaryRental> getSalary(PaginationDTO paginationDTO, Map<String, Object> queryParam, List<String> bizKeys, List<String> childrenId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select c.agency_id id, c.agency_name agencyName ,c.agency_code agencyCode,c.remark remark, ")
                .append(" c.is_month_end, c.salary_month month , ");

        if (!StringUtils.isEmpty(queryParam)) {
            sql.append(" sum(b.").append(queryParam.get("s2")).append(")  yfgzze,");

            sql.append(" sum(b.").append(queryParam.get("s1")).append(")  sfgzze ");

            sql.append(" from ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" as b")
                    .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" as pe ")
                    .append(" on  b.person_id=pe.person_id and b.agency_id=pe.agency_id ")
                    .append(" join ").append(TableName.AGENCY_VERIFY).append(" as c")
                    .append(" on b.agency_id=c.agency_id and pe.agency_id=c.agency_id ")

                    .append(" where  c.is_deleted=? AND pe.`is_deleted` = 0 ")
                    .append(" and c.is_thirteen=? and pe.is_stop_salary =? ")
                    .append("  and c.is_month_end=?");
            paramList.add(SystemDictConstant.IS_FALSE);
            paramList.add(SystemDictConstant.IS_FALSE);
            paramList.add(SystemDictConstant.IS_FALSE);
            paramList.add(SystemDictConstant.IS_FALSE);

            sql.append(" and ").append(SysUtils.inSql("c.biz_key", bizKeys));
//            sql.append(wheresql);
            sql.append(" and ").append(SysUtils.inSql("b.agency_id", childrenId));

            if (!StringUtils.isEmpty(queryParam.get("dwmc"))) {
                sql.append(" and c.agency_name like concat( '%', ?, '%') ");
                paramList.add(queryParam.get("dwmc"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bddw"))) {
                if ("0".equals(queryParam.get("bddw"))) {
                    sql.append(" and c.is_salary_change = 0 ");
                } else {
                    sql.append(" and c.is_salary_change != 0 ");
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("rylx"))) {
                sql.append(" and pe.state = ? ");
                paramList.add(queryParam.get("rylx"));
            }
            if (!StringUtils.isEmpty(queryParam.get("gzlx"))) {
                sql.append(" and pe.merit_pay_code = ? ");
                paramList.add(queryParam.get("gzlx"));
            }
            if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
                sql.append(" and c.mof_div_code = ? ");
                paramList.add(queryParam.get("mofDivCode"));
            }
        }
        sql.append("  group by c.agency_id, c.agency_code, c.agency_name, c.remark, c.is_month_end, c.salary_month");
        sql.append(" order by  c.agency_code");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(SalaryRental.class), params);
    }

    @Override
    public int getSalaryNum(Map<String, Object> queryParam, List<String> bizKeys, List<String> childrenId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from (select c.id, c.agency_name agencyName ,c.agency_code agencyCode,c.remark remark,")
                .append(" c.is_month_end, c.salary_month month , ");
        if (!StringUtils.isEmpty(queryParam)) {
            sql.append(" sum(b.").append(queryParam.get("s2")).append(")  yfgzze,");

            sql.append(" sum(b.").append(queryParam.get("s1")).append(") sfgzze ");

            sql.append(" from ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" as b")
                    .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" as pe ")
                    .append(" on  b.person_id=pe.person_id and b.agency_id=pe.agency_id ")
                    .append(" join ").append(TableName.AGENCY_VERIFY).append(" as c")
                    .append(" on b.agency_id=c.agency_id and pe.agency_id=c.agency_id ")
                    .append(" where  c.is_deleted=? AND pe.`is_deleted` = 0 ")
                    .append(" and c.is_thirteen=? and pe.is_stop_salary =? ")
                    .append("  and c.is_month_end=? ");
            paramList.add(SystemDictConstant.IS_FALSE);
            paramList.add(SystemDictConstant.DISABLE);
            paramList.add(SystemDictConstant.DISABLE);
            paramList.add(SystemDictConstant.DISABLE);
            sql.append(" and ").append(SysUtils.inSql("c.biz_key", bizKeys));

            sql.append(" and ").append(SysUtils.inSql("b.agency_id", childrenId));

            if (!StringUtils.isEmpty(queryParam.get("dwmc"))) {
                sql.append(" and a.agency_name like concat( '%', ?, '%') ");
                paramList.add(queryParam.get("dwmc"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bddw"))) {
                if ("0".equals(queryParam.get("bddw"))) {
                    sql.append(" and c.is_salary_change = 0 ");
                } else {
                    sql.append(" and c.is_salary_change != 0 ");
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("rylx"))) {
                sql.append(" and pe.state = ? ");
                paramList.add(queryParam.get("rylx"));
            }
            if (!StringUtils.isEmpty(queryParam.get("gzlx"))) {
                sql.append(" and pe.merit_pay_code = ? ");
                paramList.add(queryParam.get("gzlx"));
            }
            if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
                sql.append(" and c.mof_div_code = ? ");
                paramList.add(queryParam.get("mofDivCode"));
            }
        }
        sql.append("  group by c.id, c.agency_code, c.agency_name, c.agency_code, c.remark, c.is_month_end, c.salary_month) c");
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }

    /**
     * 获取未月结的分页数据
     *
     * @param paginationDTO
     * @param queryParam
     * @return
     */
    @Override
    public List<SalaryRental> getSalaryMonthEndPage(PaginationDTO paginationDTO, Map<String, Object> queryParam, List<String> childrenIds) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append("select av.agency_id agencyId, av.is_month_end isMonthEnd, av.agency_name agencyName ,av.agency_code agencyCode,")
                .append(" av.salary_month month, av.salary_info_verified_code,av.biz_key,");
        sql.append(" ifnull(yfgzze,0) yfgzze, ");
        sql.append(" ifnull(sfgzze,0) sfgzze ");
        sql.append("from (select b.agency_id,sum(b.s2) yfgzze,sum(b.s1) sfgzze  from ").append(TableName.PERSON_EXT_SUBMIT).append(" as pes ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" as b")
                .append(" on b.agency_id = pes.agency_id  and b.person_id=pes.person_id where b.mof_div_code=? ");
        paramList.add(queryParam.get("province"));
        sql.append(" group by b.agency_id ) a ");
        sql.append(" right join ").append(TableName.AGENCY_VERIFY).append(" as av")
                .append(" on a.agency_id = av.agency_id");
        sql.append(" where av.is_month_end = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and ").append(SysUtils.inSql("av.agency_id", childrenIds));
        if(!CollectionUtils.isEmpty(queryParam)){
            if (!StringUtils.isEmpty(queryParam.get("province"))) {
                paramList.add(queryParam.get("province"));
                sql.append(" and av.mof_div_code = ? ");
            }
        }
        sql.append(" and av.salary_month in (select distinct salary_month from sal_agency_verify where mof_div_code=? and is_salary_submit=1 ) ");
        paramList.add(queryParam.get("province"));
        sql.append(" order by av.is_salary_submit,av.salary_month, av.agency_code");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(SalaryRental.class), params);
    }

    /**
     * 未月结条数
     * @param queryParam
     * @param childrenIds
     * @return
     */
    @Override
    public int getSalaryMonthEndTotal(Map<String, Object> queryParam, List<String> childrenIds) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append("select count(*) from  ").append(TableName.AGENCY_VERIFY).append(" as av");
        sql.append(" where av.is_month_end = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and ").append(SysUtils.inSql("av.agency_id", childrenIds));
        sql.append(" and av.mof_div_code = ? ");
        paramList.add((queryParam.get("province")));
        sql.append(" and av.salary_month in (select distinct salary_month from sal_agency_verify where mof_div_code=? and is_salary_submit=1 ) ");
        paramList.add((queryParam.get("province")));

        return this.getJdbcTemplate().queryForObject(sql.toString(), paramList.toArray(), Integer.class);
    }

//    @Override
//    public int getSalaryMonthEndTotal(Map<String, Object> queryParam, List<String> childrenIds) {
//        StringBuilder sql = new StringBuilder();
//        List<Object> paramList = new ArrayList<>();
//        sql.append("select count(*) from ( ");
//        sql.append("select av.agency_id agencyId, av.is_month_end isMonthEnd, av.agency_name agencyName ,av.agency_code agencyCode,")
//                .append(" av.salary_month month, av.salary_info_verified_code,av.biz_key,");
//        sql.append(" sum(b.s2) yfgzze, ");
//        sql.append(" sum(b.s1) sfgzze ");
//        sql.append(" from ").append(TableName.PERSON_EXT_SUBMIT).append(" as pes ")
//                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" as b")
//                .append(" on b.agency_id = pes.agency_id  and b.person_id=pes.person_id");
//        sql.append(" right join ").append(TableName.AGENCY_VERIFY).append(" as av")
//                .append(" on pes.agency_id = av.agency_id");
//        sql.append(" where av.is_month_end = ? ");
//        paramList.add(SystemDictConstant.IS_FALSE);
//        sql.append(" and ").append(SysUtils.inSql("av.agency_id", childrenIds));
//        if(!CollectionUtils.isEmpty(queryParam)){
//            if (!StringUtils.isEmpty(queryParam.get("province"))) {
//                paramList.add(queryParam.get("province"));
//                sql.append(" and av.mof_div_code = ? ");
//            }
//        }
//        sql.append(" group by av.agency_id, av.is_month_end, av.agency_name, av.agency_code, av.salary_month, av.salary_info_verified_code,av.biz_key");
//        sql.append(" ) c ");
//        if (!StringUtils.isEmpty(queryParam.get("province"))) {
//            return this.getJdbcTemplate().queryForObject(sql.toString(), new Object[]{SystemDictConstant.IS_FALSE,  queryParam.get("province")}, Integer.class);
//
//        } else {
//            return this.getJdbcTemplate().queryForObject(sql.toString(), new Object[]{SystemDictConstant.IS_FALSE}, Integer.class);
//        }
//    }


















//    @Override
//    public int getSalaryMonthEndTotal(Map<String, Object> queryParam, List<String> childrenIds) {
//        StringBuilder sql = new StringBuilder();
//        sql.append(" select count(*) from (select a.agency_name agencyNamed ,a.agency_code agencyName,");
//        sql.append(" sum(b.s2) yfgzze, ");
//        sql.append(" sum(b.s1) sfgzze ");
//        sql.append(" from ").append(TableName.AGENCY_INFO).append(" as a")
//                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" as b")
//                .append(" on b.agency_id = a.id ");
//        sql.append(" left join ").append(TableName.AGENCY_VERIFY).append(" as av")
//                .append(" on a.id = av.agency_id");
//        sql.append(" where av.is_month_end = ? ");
//        if (!StringUtils.isEmpty(queryParam.get("province"))) {
//            sql.append(" and a.mof_div_code = ?");
//        }
//        sql.append(" and ").append(SysUtils.inSql("a.id", childrenIds));
//        sql.append(" GROUP BY a.agency_name,a.agency_code ) c");
//        Object[] params = null;
//        if (!StringUtils.isEmpty(queryParam.get("province"))) {
//            return this.getJdbcTemplate().queryForObject(sql.toString(), new Object[]{SystemDictConstant.IS_FALSE,  queryParam.get("province")}, Integer.class);
//
//        } else {
//            return this.getJdbcTemplate().queryForObject(sql.toString(), new Object[]{SystemDictConstant.IS_FALSE}, Integer.class);
//        }
//    }


    /**
     * 获取月结的分页数据
     *
     * @param paginationDTO
     * @param queryParam
     * @return
     */
    @Override
    public List<SalaryRental> getSalaryMonthHistoryPage(PaginationDTO paginationDTO, Map<String, Object> queryParam, List<String> childrenIds) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append(" SELECT av.agency_id AS agencyId,'1' isMonthEnd,av.agency_name AS agencyName,av.agency_code agencyCode,")
                .append("av.salary_month AS MONTH,ifnull(a.yfgzze,0) yfgzze, ifnull(a.sfgzze,0) sfgzze ")
                .append(" FROM ");
        sql.append("( select a.id agency_id, '1' isMonthEnd, a.agency_name agencyName ,a.agency_code agencyCode,")
                .append(" b.salary_month month, ");
        sql.append(" sum(b.s2) yfgzze, ");
        sql.append(" sum(b.s1) sfgzze ");
        sql.append(" from ").append(TableName.AGENCY_INFO).append(" as a")
                .append(" join ").append(TableName.SALARY_HISTORY).append(" as b")
                .append(" on b.agency_id = a.id ");
        sql.append(" where a.mof_div_code=? ");
        paramList.add((queryParam.get("province")));
        sql.append(" GROUP BY a.id,a.agency_name,a.agency_code,b.salary_month  ) a ");
        sql.append(" right join ").append(TableName.AGENCY_VERIFY).append(" as av")
                .append(" on a.agency_id = av.agency_id  and  a.MONTH=av.salary_month ");
        sql.append(" where av.is_month_end = ? ");
        paramList.add(SystemDictConstant.IS_TRUE);
        sql.append("and av.mof_div_code = ? ");
        paramList.add((queryParam.get("province")));
        sql.append(" and ").append(SysUtils.inSql("av.agency_id", childrenIds));
        if (!CollectionUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("month"))) {
                sql.append(" and av.salary_month = ? ");
                paramList.add(queryParam.get("month"));
            }
        }
//        sql.append(" group by a.id, a.agency_name,a.agency_code, b.salary_month");
        sql.append(" order by av.agency_code,av.is_salary_submit,av.salary_month ");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(SalaryRental.class), params);
    }

    /**
     * 月结条数
     * @param queryParam
     * @param childrenIds
     * @return
     */
    @Override
    public int getSalaryMonthHistoryTotal(Map<String, Object> queryParam, List<String> childrenIds) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append(" SELECT count(*) ")
                .append(" FROM ").append(TableName.AGENCY_VERIFY).append(" as av");
        sql.append(" where av.is_month_end = ? ");
        paramList.add(SystemDictConstant.IS_TRUE);
        sql.append("and av.mof_div_code = ? ");
        paramList.add((queryParam.get("province")));
        sql.append(" and ").append(SysUtils.inSql("av.agency_id", childrenIds));
        if (!CollectionUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("month"))) {
                sql.append(" and av.salary_month = ? ");
                paramList.add(queryParam.get("month"));
            }
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), paramList.toArray(), Integer.class);
    }

//    @Override
//    public int getSalaryMonthHistoryTotal(Map<String, Object> queryParam, List<String> childrenIds) {
//        StringBuilder sql = new StringBuilder();
//        List<Object> paramList = new ArrayList<>();
//        sql.append(" SELECT count(*) ")
//                .append(" FROM ");
//        sql.append("( select a.id agency_id, '1' isMonthEnd, a.agency_name agencyName ,a.agency_code agencyCode,")
//                .append(" b.salary_month month, ");
//        sql.append(" sum(b.s2) yfgzze, ");
//        sql.append(" sum(b.s1) sfgzze ");
//        sql.append(" from ").append(TableName.AGENCY_INFO).append(" as a")
//                .append(" join ").append(TableName.SALARY_HISTORY).append(" as b")
//                .append(" on b.agency_id = a.id ");
//        sql.append(" where a.mof_div_code=? ");
//        paramList.add((queryParam.get("province")));
//        sql.append(" GROUP BY a.id,a.agency_name,a.agency_code,b.salary_month  ) a ");
//        sql.append(" right join ").append(TableName.AGENCY_VERIFY).append(" as av")
//                .append(" on a.agency_id = av.agency_id  and  a.MONTH=av.salary_month ");
//        sql.append(" where av.is_month_end = ? ");
//        paramList.add(SystemDictConstant.IS_TRUE);
//        sql.append("and av.mof_div_code = ? ");
//        paramList.add((queryParam.get("province")));
//        sql.append(" and ").append(SysUtils.inSql("av.agency_id", childrenIds));
//        if (!CollectionUtils.isEmpty(queryParam)) {
//            if (!StringUtils.isEmpty(queryParam.get("month"))) {
//                sql.append(" and av.salary_month = ? ");
//                paramList.add(queryParam.get("month"));
//            }
//        }
//       // sql.append(" order by av.agency_code,av.is_salary_submit,av.salary_month ");
//        //请求参数初始化
//        return this.getJdbcTemplate().queryForObject(sql.toString(), paramList.toArray(), Integer.class);
//    }

/*    @Override
    public int getSalaryMonthHistoryTotal(Map<String, Object> queryParam, List<String> childrenIds) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append(" select count(*) from (select a.agency_code agencyCode ,a.agency_name agencyName,");
        sql.append(" sum(b.s2) yfgzze, ");
        sql.append(" sum(b.s1) sfgzze ");
        sql.append(" from ").append(TableName.AGENCY_INFO).append(" as a")
                .append(" join ").append(TableName.SALARY_HISTORY).append(" as b")
                .append(" on b.agency_id = a.id ");
        sql.append(" where ").append(SysUtils.inSql("a.id", childrenIds));
        if (!CollectionUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("month"))) {
                sql.append(" and b.salary_month = ? ");
                paramList.add(queryParam.get("month"));
            }
        }
        sql.append(" GROUP BY a.agency_name,a.agency_code, b.salary_month ) c");
        return this.getJdbcTemplate().queryForObject(sql.toString(), paramList.toArray(), Integer.class);
    }*/

    @Override
    public List<Map<String, Object>> selectSalaryHistoryPage(String agencyId,
                                                             String salaryMonth,
                                                             List<SalaryColumnWithStateVO> columnList,String mofDivCode) {
        StringBuilder sb = new StringBuilder();
        List<Object> conditions = new ArrayList<>();
        StringBuilder s1Column = new StringBuilder();
        StringBuilder salaryColumn = new StringBuilder();
        StringBuilder ifNullsalaryColumn = new StringBuilder();
        if (!CollectionUtils.isEmpty(columnList)) {
            s1Column.append(" ( ");
            for (SalaryColumnWithStateVO columnWithState : columnList) {
                String column = columnWithState.getColumnName();
//                判断是否为代扣或代缴项
                boolean isMinus = columnWithState.getIsAccsalPay().equals(1)
                        || columnWithState.getIsAccsalWithhold().equals(1);
                if (isMinus) {
                    s1Column.append(" - ").append(column);
                } else {
                    s1Column.append(" + ").append(column);
                }
                salaryColumn.append(" , ").append(column);
                ifNullsalaryColumn.append(" , ifNull(").append(column).append(", 0) ").append(column);
            }
            s1Column.append(" ) s1 ");
        }
        sb.append(" select ")
                .append(" @i:=@i + 1 `index`, salaryMonth, deptId, personId, idenNo, personName, ")
                .append(s1Column)
                .append(salaryColumn)
                .append(" from ")
                .append(" (select ")
                .append(" ifNull(shy.salary_month, ?) salaryMonth, pet.dept_id deptId, pio.id personId, pio.iden_no idenNo, pio.person_name personName ");
        conditions.add(salaryMonth);
        sb.append(ifNullsalaryColumn)
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        conditions.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_HISTORY).append(" shy ")
                .append(" on ").append(" shy.person_id = pio.id and shy.salary_month = ? ");
        conditions.add(salaryMonth);
        sb.append(" where ").append(" pio.is_deleted = 0 ");
                if(!StringUtils.isEmpty(mofDivCode)){
                   sb.append(" and pio.mof_div_code = ?");
                    conditions.add(mofDivCode);
                }
               sb.append(" order by ").append(" pio.update_time desc ")
                .append(" ) t1, (select @i:=0) t2 ");
//        String sql = boxUpSql(sb.toString());
//        Object[] params = boxUpParam(conditions.toArray(), paginationDTO);
        return super.getjdbcTemplate().queryForList(sb.toString(), conditions.toArray());
    }

    @Override
    public Map<String, Object> selectSalaryHistorySum(String agencyId,
                                                      String salaryMonth,
                                                      List<SalaryColumnWithStateVO> columnList,String mofDivCode) {
        StringBuilder sb = new StringBuilder();
        List<Object> conditions = new ArrayList<>();
        StringBuilder s1Column = new StringBuilder();
        StringBuilder salaryColumn = new StringBuilder();
        StringBuilder sumSalaryColumn = new StringBuilder();
        if (!CollectionUtils.isEmpty(columnList)) {
            s1Column.append(" ( ");
            for (SalaryColumnWithStateVO columnWithState : columnList) {
                String column = columnWithState.getColumnName();
//                判断是否为代扣或代缴项
                boolean isMinus = columnWithState.getIsAccsalPay().equals(1)
                        || columnWithState.getIsAccsalWithhold().equals(1);
                if (isMinus) {
                    s1Column.append(" - ").append(column);
                } else {
                    s1Column.append(" + ").append(column);
                }
                salaryColumn.append(" , ").append(column);
                sumSalaryColumn.append(" , ifNull(sum(").append(column).append("), 0) ").append(column);
            }
            s1Column.append(" ) s1 ");
        }
        sb.append(" select ")
                .append(s1Column)
                .append(salaryColumn)
                .append(" from ")
                .append(" ( select ")
                .append(" ifNull(sum(s1),0) s1 ")
                .append(sumSalaryColumn)
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        conditions.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_HISTORY).append(" shy ")
                .append(" on ").append(" shy.person_id = pio.id and shy.salary_month = ? ");
        conditions.add(salaryMonth);
        sb.append(" where ").append(" pio.is_deleted = 0  ");
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pio.mof_div_code");
            conditions.add(mofDivCode);
        }
        sb.append(" ) t");
        return super.getjdbcTemplate().queryForMap(sb.toString(), conditions.toArray());
    }

    @Override
    public int selectSalaryHistoryTotal(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" count(*) ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 and pet.is_stop_salary = 0 and pet.agency_id = ? ")
                .append(" where ").append(" pio.is_deleted = 0 ");
        return super.getjdbcTemplate().queryForObject(sb.toString(), int.class, agencyId);
    }

    @Override
    public List<SalaryRental> getSalaryMonth(Map<String, Object> queryParam, List<String> childrenIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.id agencyId, av.is_month_end isMonthEnd, a.agency_name agencyName ,a.agency_code agencyCode,")
                .append(" av.salary_month month, av.salary_info_verified_code,");
        sql.append(" sum(b.s2) yfgzze, ");
        sql.append(" sum(b.s1) sfgzze ");
        sql.append(" from ").append(TableName.AGENCY_INFO).append(" as a")
                .append(" left join ").append(TableName.SALARY_MONTH_END).append(" as b")
                .append(" on b.agency_id = a.id ");
        sql.append(" left join ").append(TableName.AGENCY_VERIFY).append(" as av")
                .append(" on a.id = av.agency_id");
        sql.append(" where av.is_month_end = ?");
        sql.append(" and ").append(SysUtils.inSql("a.id", childrenIds));
        sql.append(" group by a.id, av.is_month_end, a.agency_name, a.agency_code, av.salary_month, av.salary_info_verified_code");
        sql.append(" order by av.salary_month desc, a.agency_code");
        ;
        //请求参数初始化
        Object[] params = new Object[]{SystemDictConstant.IS_FALSE};
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(SalaryRental.class),params);
    }

    @Override
    public List<SalaryRental> getSalaryMonthHistory(Map<String, Object> queryParam, List<String> childrenIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.id agencyId, '1' isMonthEnd, a.agency_name agencyName ,a.agency_code agencyCode,")
                .append(" b.salary_month month, ");
        sql.append(" sum(b.s2) yfgzze, ");
        sql.append(" sum(b.s1) sfgzze ");
        sql.append(" from ").append(TableName.AGENCY_INFO).append(" as a")
                .append(" join ").append(TableName.SALARY_HISTORY).append(" as b")
                .append(" on b.agency_id = a.id ");
        sql.append(" where ").append(SysUtils.inSql("a.id", childrenIds));
        sql.append(" group by a.id, a.agency_name,a.agency_code, b.salary_month");
        sql.append(" order by b.salary_month desc , a.agency_code ");

        //请求参数初始化

        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(SalaryRental.class));
    }

    @Override
    public SalaryHistory selectHistorySalaryByPersonId(String personId, String agencyId, String salaryMonth) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" id,  history_key, create_time, update_time,mof_div_code, salary_month, agency_id, person_id, is_thirteen,")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, ")
                .append(" s39, s40, s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, ")
                .append(" s57, s58, s59, s60, s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, ")
                .append(" s75, s76, s77, s78, s79, s80, s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, ")
                .append(" s93, s94, s95, s96, s97, s98, s99, s100 ")
                .append(" from ")
                .append(TableName.SALARY_HISTORY)
                .append(" where ").append(" person_id = ? and agency_id = ? and salary_month = ? ");
        Object[] params = {
                personId, agencyId, salaryMonth
        };
        try {
            return super.getJdbcTemplate().queryForObject(sb.toString(), params, new BeanPropertyRowMapper<>(SalaryHistory.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }


    @Override
    public List<SalaryMonthEndVO> getSalaryMonthHistory(String month, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append("select ")
                .append(" id, create_time, update_time, agency_id, person_id,is_thirteen,mof_div_code,")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, ")
                .append(" s39, s40, s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, ")
                .append(" s57, s58, s59, s60, s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, ")
                .append(" s75, s76, s77, s78, s79, s80, s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, ")
                .append(" s93, s94, s95, s96, s97, s98, s99, s100 from ").append(TableName.SALARY_HISTORY)
                .append(" where ").append(SysUtils.inSql("agency_id",agencyIds))
                .append(" and salary_month=? ");
        Object[] params = new Object[]{month};
        return this.getJdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper(SalaryMonthEndVO.class),params);
    }
    @Override
    public List<SalaryMonthEndVO> querySalaryMonthHistory(String month, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append("select ")
                .append(" history_key as id, create_time, update_time, agency_id, person_id,is_thirteen,mof_div_code,")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, ")
                .append(" s39, s40, s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, ")
                .append(" s57, s58, s59, s60, s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, ")
                .append(" s75, s76, s77, s78, s79, s80, s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, ")
                .append(" s93, s94, s95, s96, s97, s98, s99, s100 from ").append(TableName.SALARY_HISTORY)
                .append(" where ").append(SysUtils.inSql("agency_id",agencyIds))
                .append(" and salary_month=? ");
        Object[] params = new Object[]{month};
        return this.getJdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper(SalaryMonthEndVO.class),params);
    }
}
